

-- drop table Game;
CREATE TABLE Game
 ( 
    Game_ID numeric(10,0) NOT NULL IDENTITY, 
    Name varchar(20) NOT NULL, 
    StartingTime dateTime  NOT NULL DEFAULT GETDATE(),         
    PlayerCount int NOT NULL,
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT CHK_Game_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_Game PRIMARY KEY CLUSTERED (Game_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table Player;
CREATE TABLE Player
 ( 
    Player_ID numeric(10,0) NOT NULL IDENTITY,
    Name varchar(20) NOT NULL, 
    [Type] varchar(20) NOT NULL ,         
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT CHK_Player_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_Player PRIMARY KEY CLUSTERED (Player_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table PlayerEnrollment;
CREATE TABLE PlayerEnrollment
 ( 
    PlayerEnrollment_ID numeric(10,0) NOT NULL IDENTITY,
    Name varchar(20),          
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    Game_ID numeric(10,0) NOT NULL, 
    Player_ID numeric(10,0) NOT NULL,
    Player_Dice nvarchar(20) DEFAULT NULL,
    SequenceNo int NOT NULL DEFAULT 0,
    IsMyMove nvarchar(1) NOT NULL DEFAULT 'N',
    IsInJail nvarchar(1) NOT NULL DEFAULT 'N',
    Position int NOT NULL DEFAULT 0,
    CONSTRAINT CHK_PlayerEnrollment_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT CHK_PlayerEnrollment_IsMyMove CHECK (IsActive = 'Y' OR IsActive='N'),
    FOREIGN KEY (Game_ID) REFERENCES Game(Game_ID),
    FOREIGN KEY (Player_ID) REFERENCES Player(Player_ID),
    CONSTRAINT PK_PlayerEnrollment PRIMARY KEY CLUSTERED (PlayerEnrollment_ID ASC) ON [PRIMARY]
 )
GO

-- drop table Land;
CREATE TABLE Land
 ( 
    Land_ID numeric(10,0) NOT NULL IDENTITY,
    Name varchar(50) NOT NULL, 
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    [Type] varchar(20) NOT NULL,
    Color varchar(20) DEFAULT 'RED',
    [Group] int NOT NULL DEFAULT 0,
    Position int NOT NULL DEFAULT 0,
    CONSTRAINT CHK_Land_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_Land PRIMARY KEY CLUSTERED (Land_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table Deed;
CREATE TABLE Deed
 ( 
    Deed_ID numeric(10,0) NOT NULL IDENTITY,
    Land_ID numeric(10,0) NOT NULL,
    [Description] varchar(255),        
    IsSold varchar(1) NOT NULL DEFAULT 'N',
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    Price numeric(10,0) NOT NULL DEFAULT 100,
    Rent numeric(10,0) NOT NULL DEFAULT 10,
    IsMortgaged varchar(1) DEFAULT 'N',
    MortgagedValue numeric(10,0) NOT NULL DEFAULT 50,
    HouseCount int NOT NULL DEFAULT 0,
    HotelCount int NOT NULL DEFAULT 0,
    FOREIGN KEY (Land_ID) REFERENCES Land(Land_ID),
    CONSTRAINT CHK_Deed_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_Deed PRIMARY KEY CLUSTERED (Deed_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table DeedSetting;
CREATE TABLE DeedSetting
 ( 
    DeedSetting_ID numeric(10,0) NOT NULL IDENTITY, 
    Deed_ID numeric(10,0) NOT NULL,
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    RentForHouseCount numeric(10,0) NOT NULL DEFAULT 10,
    RentForHotelCount numeric(10,0) NOT NULL DEFAULT 10,
    FOREIGN KEY (Deed_ID) REFERENCES Deed(Deed_ID),
    CONSTRAINT CHK_DeedSetting_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_DeedSetting PRIMARY KEY CLUSTERED (DeedSetting_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table GameCard;
CREATE TABLE GameCard
 ( 
    GameCard_ID numeric(10,0) NOT NULL IDENTITY,
    Name varchar(20) NOT NULL, 
    [Type] varchar(20) NOT NULL,  
    [Description] varchar(255),             
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT CHK_GameCard_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_GameCard PRIMARY KEY CLUSTERED (GameCard_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table GameCardAction;
CREATE TABLE GameCardAction
 ( 
    GameCardAction_ID numeric(10,0) NOT NULL IDENTITY,
    GameCard_ID numeric(10,0) NOT NULL,
    [Type] varchar(20) NOT NULL,  
    [Description] varchar(255),             
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    FOREIGN KEY (GameCard_ID) REFERENCES GameCard(GameCard_ID),
    CONSTRAINT CHK_GameCardAction_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_GameCardAction PRIMARY KEY CLUSTERED (GameCardAction_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table PlayerAsset;
CREATE TABLE PlayerAsset
 ( 
    PlayerAsset_ID numeric(10,0) NOT NULL IDENTITY,      
    Game_ID numeric(10,0) NOT NULL,
    Player_ID numeric(10,0) NOT NULL,
    Land_ID numeric(10,0),
    GameCard_ID numeric(10,0),
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    Currency numeric(10,0) NOT NULL DEFAULT 1500,
    AssetType varchar(20) NOT NULL DEFAULT 'Land',
    FOREIGN KEY (Game_ID) REFERENCES Game(Game_ID),
    FOREIGN KEY (Player_ID) REFERENCES Player(Player_ID),
    FOREIGN KEY (Land_ID) REFERENCES Land(Land_ID),
    FOREIGN KEY (GameCard_ID) REFERENCES GameCard(GameCard_ID),
    CONSTRAINT CHK_PlayerAsset_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_PlayerAsset PRIMARY KEY CLUSTERED (PlayerAsset_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table Standing;
CREATE TABLE Standing
 ( 
    Standing_ID numeric(10,0) NOT NULL IDENTITY,          
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT CHK_Standing_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_Standing PRIMARY KEY CLUSTERED (Standing_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table History;
CREATE TABLE History
 ( 
    History_ID numeric(10,0) NOT NULL IDENTITY,
    Name varchar(20), 
    [Description] varchar(255),
    [Type] varchar(20) NOT NULL,         
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT CHK_History_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_History PRIMARY KEY CLUSTERED (History_ID ASC) 
    ON [PRIMARY] 
 )
GO


-- drop table GameAction;
CREATE TABLE GameAction
 ( 
    GameAction_ID numeric(10,0) NOT NULL IDENTITY,      
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    LandType nvarchar(20) NOT NULL,
    LandOwnerType nvarchar(20) NOT NULL,
    GameActionKey nvarchar(50) NOT NULL,
    CONSTRAINT CHK_GameAction_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_GameAction PRIMARY KEY CLUSTERED (GameAction_ID ASC) 
    ON [PRIMARY] 
 )
GO

-- drop table Layout;
CREATE TABLE Layout
 ( 
    Layout_ID numeric(10,0) NOT NULL IDENTITY, 
    Name varchar(20),
    [Row] int DEFAULT 0,
    [Column] int DEFAULT 0,
    Land_ID numeric(10,0),
    SequenceNo int DEFAULT 0,
    Created dateTime  NOT NULL DEFAULT GETDATE(), 
    Updated dateTime  NOT NULL DEFAULT GETDATE(),        
    IsActive varchar(1) NOT NULL DEFAULT 'Y',
    FOREIGN KEY (Land_ID) REFERENCES Land(Land_ID),
    CONSTRAINT CHK_Layout_IsActive CHECK (IsActive = 'Y' OR IsActive='N'),
    CONSTRAINT PK_Layout PRIMARY KEY CLUSTERED (Layout_ID ASC) 
    ON [PRIMARY] 
 )
GO